﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class ActivityRequestDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);       
        ConvertNullable cn = new ConvertNullable();

        public DataSet GetActRequest(Nullable<int> act_seq, Nullable<int> granted)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(3);

            dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
            dbManager.AddParameters(1, "@granted", granted);
            dbManager.AddParameters(2, "@act_seq", act_seq); 

            string sql = "SELECT e.first_name"
                    + " ,e.last_name"
                    + " ,e.name_prefix_seq"
                    + " ,ar.act_request_seq"
                    + " ,ar.act_seq"
                    + " ,ar.emp_seq"
                    + " ,ar.granted"
                    + " ,ar.act_request_status"
                    + " ,ar.remarks"
                    + " ,mp.name_prefix_seq"
                    + " ,mp.prefix_name"
                    + " ,p.pos_seq"
                    + " ,p.pos_name"
                    + " ,j.job_site_name"
                    + " ,mq.edu_qual_name"
                    + " ,eh.edu_end_year"
                    + " ,concat( mp.prefix_name,e.first_name,'  ',e.last_name) AS full_name"
                    + ", c.class_name"
                    + " ,concat( mp.prefix_name,e.first_name,'  ',e.last_name, ' ', p.pos_name, ' ', c.class_name) AS emp_name"
                    + " FROM hrm_ac_activity_requests ar"
                    + " left Join hrm_ps_employees e ON ar.emp_seq = e.emp_seq"
                    + " left join hrm_mp_classes c on e.class_seq = c.class_seq"
                    + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                    + " Left Join hrm_mp_job_sites j ON p.job_site_seq = j.job_site_seq"
                    + " left Join mas_name_prefixes mp ON e.name_prefix_seq = mp.name_prefix_seq"  
                    + " Left Join hrm_ps_edu_histories AS eh ON ar.emp_seq = eh.emp_seq"
                    + " Left Join mas_edu_qualifications AS mq ON eh.edu_qual_seq = mq.edu_qual_seq"  
                    + " WHERE ar.record_status not like @delete_status "
                    + " AND ar.granted not like 2"                    
                    + " AND eh.edu_end_year =  (SELECT MAX(hrm_ps_edu_histories.edu_end_year ) FROM hrm_ps_edu_histories WHERE hrm_ps_edu_histories.emp_seq = ar.emp_seq )"
                    + (granted != null ? " AND ar.granted = @granted" : "")
                    //+ (granted != null ? " AND ar.granted = @granted" : " AND ar.granted is null")
                    + (act_seq != null ? " And ar.act_seq = @act_seq" : "");

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;

        }
        public DataSet GetActHistory(Nullable<int> emp_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(3);

            dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@emp_seq", emp_seq);
            dbManager.AddParameters(2, "@DateNow", DateTime.Today);

            string sql = "SELECT ar.act_request_seq"
                    + " ,ar.granted"
                    + " ,a.act_seq"
                    + " ,a.act_descr"
                    + " ,a.date_activity_start"
                    + " ,a.date_activity_end"
                    + " FROM hrm_ac_activity_requests AS ar"
                    + " Inner Join hrm_ac_activitys AS a ON a.act_seq = ar.act_seq"
                    + " WHERE ar.record_status = @record_status"
                    + " AND ar.emp_seq =  @emp_seq"
                    + " AND ar.granted =  '1'" 
                    + " AND a.date_activity_end <=  @DateNow";


            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;

        }
        public DataSet GetActRequestUpper(Nullable<int> ActivitySequence, Nullable<int> granted)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(3);

            dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
            dbManager.AddParameters(1, "@granted", granted);
            dbManager.AddParameters(2, "@act_seq", ActivitySequence);

            string sql = "SELECT CONCAT( mp.PREFIX_NAME,eu.FIRST_NAME,'  ',eu.LAST_NAME) AS UPPER_FULL_NAME"
                    + ", c.CLASS_NAME, eu.EMP_SEQ AS UPPER_EMP_SEQ, p.working_upper_pos_seq"
                    + ", j.JOB_SITE_NAME, ex.EXEC_POS_NAME"
                    + " FROM HRM_PS_EMPLOYEES e"
                    + " LEFT JOIN HRM_AC_ACTIVITY_REQUESTS ar ON e.EMP_SEQ = ar.EMP_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS up ON p.working_upper_pos_seq = up.POS_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES eu ON up.POS_SEQ = eu.POS_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON eu.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"
                    + " LEFT JOIN HRM_MP_CLASSES c ON eu.CLASS_SEQ = c.CLASS_SEQ"
                    + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON eu.WORKING_JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES mp ON eu.NAME_PREFIX_SEQ = mp.NAME_PREFIX_SEQ"
                    + " WHERE e.RECORD_STATUS not like @delete_status "
                        + " AND es.ACTIVE not like 0"
                        + " AND p.working_upper_pos_seq IS NOT NULL"
                        + (granted != null ? " AND ar.GRANTED = @granted" : "")
                        + (ActivitySequence.HasValue ? " AND ar.ACT_SEQ = @act_seq" : "");

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;

        }

        public DataSet LoadActivityRequestForExcel(Nullable<int> ActivitySequence, Nullable<int> granted
            , Nullable<DateTime> ProjectStartDate, Nullable<DateTime> ProjectEndDate)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(5);

            dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
            dbManager.AddParameters(1, "@granted", granted);
            dbManager.AddParameters(2, "@ActivitySequence", ActivitySequence);
            dbManager.AddParameters(3, "@ProjectStartDate", ProjectStartDate.HasValue ? ProjectStartDate : SystemInfo._DefaultDateStart);
            dbManager.AddParameters(4, "@ProjectEndDate", ProjectEndDate.HasValue ? ProjectEndDate : SystemInfo._DefaultDateEnd);
            

            string sql = "SELECT CONCAT(oper.BGM_PROJ_OPER_NAME"
                            + ", '\n ระยะเวลาดำเนินการ '"
                                + ", CASE WHEN YEAR(a.DATE_ACTIVITY_START) = YEAR(a.DATE_ACTIVITY_END) THEN"
                                    + " CASE WHEN MONTH(a.DATE_ACTIVITY_START) = MONTH(a.DATE_ACTIVITY_END) THEN"
					                    + " CONCAT(CAST(DATE_FORMAT(a.DATE_ACTIVITY_START, '%e' )AS CHAR)"
                                        + ", ' - ', CAST(DATE_FORMAT(a.DATE_ACTIVITY_END, '%e')AS CHAR)"
                                        + ", ' ', CAST(F_FORMAT_MONTH_THAI(a.DATE_ACTIVITY_START)AS CHAR)"
                                        + ", ' ', YEAR(a.DATE_ACTIVITY_START))"
                                    + " ELSE CONCAT(CAST(DATE_FORMAT( a.DATE_ACTIVITY_START, '%e' )AS CHAR)"
                                        + ", '  ', CAST(F_FORMAT_MONTH_THAI( a.DATE_ACTIVITY_START)AS CHAR)"
                                        + ", ' - ', CAST(DATE_FORMAT( a.DATE_ACTIVITY_END, '%e' )AS CHAR)"
                                        + ", '  ', CAST(F_FORMAT_MONTH_THAI( a.DATE_ACTIVITY_END)AS CHAR)  )"
				                        + " END"
                                + " ELSE CONCAT(DATE_FORMAT(a.DATE_ACTIVITY_START, '%e')"
                                    + ", ' ', F_FORMAT_MONTH_THAI(a.DATE_ACTIVITY_START)"
                                    + ", ' ', YEAR(a.DATE_ACTIVITY_START)"
                                    + ", ' - ', DATE_FORMAT(a.DATE_ACTIVITY_END, '%e')"
                                    + ", ' ', F_FORMAT_MONTH_THAI(a.DATE_ACTIVITY_END)"
                                    + ", ' ', YEAR(a.DATE_ACTIVITY_END)"
                                    + ")"
                                + " END"
                            + ", ' ระยะเวลาเดินทางไปปฏิบัติราชการ '"
                                + ", (a.DATE_APP_END - a.DATE_APP_START) + 1, ' วัน'"
                            + ", '\n เดินทางระหว่างวันที่ '"
                                + ", CASE WHEN YEAR(a.DATE_APP_START) = YEAR(a.DATE_APP_END) THEN"
                                    + " CASE WHEN MONTH(a.DATE_APP_START) = MONTH(a.DATE_APP_END) THEN"
                                        + " CONCAT(CAST(DATE_FORMAT(a.DATE_APP_START, '%e' )AS CHAR)"
                                        + ", ' - ', CAST(DATE_FORMAT(a.DATE_APP_END, '%e')AS CHAR)"
                                        + ", ' ', CAST(F_FORMAT_MONTH_THAI(a.DATE_APP_START)AS CHAR)"
                                        + ", ' ', YEAR(a.DATE_APP_START))"
                                    + " ELSE CONCAT(CAST(DATE_FORMAT( a.DATE_APP_START, '%e' )AS CHAR)"
                                        + ", '  ', CAST(F_FORMAT_MONTH_THAI( a.DATE_APP_START)AS CHAR)"
                                        + ", ' - ', CAST(DATE_FORMAT( a.DATE_APP_END, '%e' )AS CHAR)"
                                        + ", '  ', CAST(F_FORMAT_MONTH_THAI( a.DATE_APP_END)AS CHAR)  )"
                                        + " END"
                                + " ELSE CONCAT(DATE_FORMAT(a.DATE_APP_START, '%e')"
                                    + ", ' ', F_FORMAT_MONTH_THAI(a.DATE_APP_START)"
                                    + ", ' ', YEAR(a.DATE_APP_START)"
                                    + ", ' - ', DATE_FORMAT(a.DATE_APP_END, '%e')"
                                    + ", ' ', F_FORMAT_MONTH_THAI(a.DATE_APP_START)"
                                    + ", ' ', YEAR(a.DATE_APP_END)"
                                    + ")"
                                + " END"
                        + ")AS PROJECT_NAME"
                    + ", null AS PRODUCT"//สินค้า"
                    + ", CONCAT(ct.CITY_NAME, ' ', co.COUNTRY_NAME) AS LOCATION"
                    + ", oper.ATTEND_COUNT AS ATTEND_COUNT"//จำนวนบริษัทผู้เข้าร่วมงาน"
                    + ", (select count(sr.emp_seq) "
                        + " from hrm_ac_activity_requests sr"
                        + " where sr.record_status not like @delete_status" 
                        + " and sr.act_seq = a.act_seq"
                        + " and sr.granted = 1"
                        + ") AS PERSON_COUNT"//จำนวนจนท"
                    + ", CONCAT(mp.PREFIX_NAME,e.FIRST_NAME,'  ',e.LAST_NAME, ' ', '(', IFNULL(j.JOB_SITE_NAME_SHORT, j.JOB_SITE_NAME),')') AS PERSON_LIST"//เจ้าหน้าที่โครงการ"
                    + " FROM HRM_AC_ACTIVITYS a"
                    + " LEFT JOIN HRM_AC_ACTIVITY_REQUESTS ar ON a.ACT_SEQ = ar.ACT_SEQ AND ar.GRANTED = @granted"
                    + " LEFT JOIN BGM_PROJECT_OPERATION oper ON a.BGM_PROJ_OPER_SEQ = oper.BGM_PROJ_OPER_SEQ"
                    + " LEFT JOIN MAS_CITIES ct ON a.CITY_SEQ = ct.CITY_SEQ"
                    + " LEFT JOIN MAS_COUNTRIES co ON ct.COUNTRY_SEQ = co.COUNTRY_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON ar.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES mp ON e.NAME_PREFIX_SEQ = mp.NAME_PREFIX_SEQ"
                    + " WHERE a.RECORD_STATUS not like @delete_status "
                    + " AND (a.DATE_ACTIVITY_START >= @ProjectStartDate OR a.DATE_ACTIVITY_END <= @ProjectEndDate )"
                    + (ActivitySequence.HasValue ? " AND ar.ACT_SEQ = @ActivitySequence" : "");

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;

        }
        public DataSet GetReqStatus(Nullable<int> emp_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(3);

            dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@emp_seq", emp_seq);
            dbManager.AddParameters(2, "@DateNow", DateTime.Today);

            string sql = "SELECT ms.send_seq"
                        + " ,ms.send_date"
                        + " ,ms.ref_seq"
                        + " ,ms.ref_type"
                        + " ,ms.ref_no"
                        + " ,ms.send_text"
                        + " ,ms.from_emp_seq"
                        + " ,ms.to_emp_seq"
                        + " ,ms.from_send_seq"
                        + " ,ms.remarks"
                        + " ,ms.message_status"
                        + " ,concat( np.prefix_name,e.first_name,'  ',e.last_name) AS from_user"
                        + " ,concat( npp.prefix_name,ee.first_name,'  ',ee.last_name) AS to_use"
                        + " ,a.granted"
                        + " ,a.act_request_seq AS Sequence"
                        + " ,CASE WHEN ms.message_status = '0' THEN 'รอการพิจารณา'"
                        + " ELSE CASE WHEN a.granted = '1' THEN 'อนุมัต'"
                             + " ELSE CASE WHEN a.granted = '2' THEN 'ไม่อนุมัติ'"
                                + " ELSE 'รอผลการพิจารณา'"
                                 + " END"
                             + " END"
                         + " END AS act_approve"

                        + ", CASE WHEN a.granted  = '2' THEN 'ไม่อนุมัติ'"
                            + " ELSE CASE WHEN a.granted =  '1' THEN 'อนุมัติ'"
                             + " ELSE CASE WHEN message_status = '0' THEN 'รอการพิจารณา'"
                                + " ELSE 'รอผลการพิจารณา'"
                                 + " END"
                             + " END"
                        + " END AS upper_approve"
                        + " ,aa.act_descr"
                        + " ,aa.date_activity_start"
                        + " FROM message_send AS ms"
                        + " Left Join hrm_ps_employees AS e ON ms.from_emp_seq = e.emp_seq"
                        + " Left Join hrm_ps_employees AS ee ON ms.to_emp_seq = ee.emp_seq"
                        + " Left Join mas_name_prefixes AS np ON e.name_prefix_seq = np.name_prefix_seq"
                        + " Left Join mas_name_prefixes AS npp ON ee.name_prefix_seq = npp.name_prefix_seq"
                        + " Left Join hrm_ac_activity_requests AS a ON ms.from_emp_seq = a.emp_seq AND ms.ref_seq =a.act_seq"
                        + " Left Join  hrm_ac_activitys aa ON a.act_seq=aa.act_seq"
                        + " WHERE ms.record_status = @record_status"
                        + (emp_seq != null ? " AND ms.from_emp_seq = @emp_seq " : "")
                        + " group by a.act_seq";


            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;

        }
        public void InsertActRequest(int act_seq, int emp_seq ,Nullable<int> granted, string remarks, int created_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@act_seq", act_seq);
                dbManager.AddParameters(1, "@granted", granted);                
                dbManager.AddParameters(2, "@act_request_status", SystemInfo._ProcessRecordStatus);
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@created_by", created_by);
                dbManager.AddParameters(6, "@created_when", DateTime.Today);
                dbManager.AddParameters(7, "@emp_seq", emp_seq);

                strSQL = "INSERT INTO hrm_ac_activity_requests"
                    + "(act_seq"
                    + ", emp_seq"
                    + ", granted"
                    + ", act_request_status"
                    + ", remarks"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@act_seq"
                    + ", @emp_seq"
                    + ", @granted"
                    + ", @act_request_status"
                    + ", @remarks"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void UpdateActRequest(int act_request_seq, Nullable<int> granted, int updated_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@act_request_seq", act_request_seq);
                dbManager.AddParameters(1, "@granted", granted);                
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);


                strSQL = "UPDATE  hrm_ac_activity_requests"
                    + " SET granted = @granted"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE act_request_seq = @act_request_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
